#!/usr/bin/env python
# -*- coding: utf-8 -*-
import mysql.connector
import uuid
from mysql.connector import pooling


def sync_driver_data():
    # 源数据库配置 (sysmandb)
    src_db = {
        # 'host': '10.1.213.159',
        # 'user': 'GTXS_user',
        # 'password': 'GTXS_user_JG123',
        'host': '10.99.81.155',
        'port': 8306,
        'user': 'root',
        'password': 'Lgmi6395',
        'database': 'jg_main'
    }

    # 目标数据库配置 (jg_usercenter)
    dest_db = {
        'host': '10.99.81.155',
        'port': 8306,
        'user': 'jg_usercenter',
        'password': 'Jg_usercenter_20250312',
        'database': 'jg_usercenter'
    }

    try:
        # 创建数据库连接
        # src_conn = mysql.connector.connect(**src_db)
        src_connection_pool = pooling.MySQLConnectionPool(
            pool_name="srcpool",
            pool_size=5,
            pool_reset_session=True,
            **src_db
        )
        src_conn = src_connection_pool.get_connection()

        # dest_conn = mysql.connector.connect(**dest_db)
        dest_connection_pool = pooling.MySQLConnectionPool(
            pool_name="destpool",
            pool_size=5,
            pool_reset_session=True,
            **dest_db
        )
        dest_conn = dest_connection_pool.get_connection()

        src_cursor = src_conn.cursor(dictionary=True)
        dest_cursor = dest_conn.cursor()

        # 1. 查询源数据,注意数据范围
        src_cursor.execute("""
            select guid,
                   tenant_id,
                   truck_driver_id,
                   truck_driver_name,
                   truck_driver_telephone,
                   truck_driver_idcardno,
                   id_info_address,
                   driver_license_num,
                   driver_license_validdate,
                   contacter,
                   contacter_phone,
                   driver_state,
                   deleted_tag,
                   create_user,
                   create_date,
                   change_user,
                   change_date
            from truck_driver where 1=1 and deleted_tag = 'N' and driver_state != 'Deleted' 
                 and truck_driver_name not in (select corp_name from jg_usercenter.sys_corp where register_type = 'person')
        """)

        # 2. 准备插入语句（根据实际表结构调整字段）

        # 3. 执行同步
        batch_size = 500
        count = 0

        roles = query_default_role(dest_conn)
        for sc in src_cursor:
            processed_user = dict(sc)  # 创建原始数据的副本
            # 使用Python函数处理特定字段

            processed_user['enterprise_name'] = sc['truck_driver_name']
            processed_user['enterprise_id'] = 'C' + sc['truck_driver_telephone']

            # 执行插入
            # insert sys_depart
            dept_id = insert_dept(processed_user['enterprise_name'], processed_user['enterprise_id'], dest_cursor)
            processed_user['depart_ids'] = dept_id

            user = query_user(processed_user['truck_driver_id'], dest_conn)
            if user is not None:
                processed_user['user_id'] = user['id']
                processed_user['user_code'] = user['user_code']
                # update sys_user
                update_user_corp_dept(user['id'], processed_user['enterprise_id'], dept_id, dest_cursor)
            else:
                user = query_user(processed_user['truck_driver_telephone'], dest_conn)
                if user is not None:
                    processed_user['user_id'] = user['id']
                    processed_user['user_code'] = user['user_code']
                    # update sys_user
                    update_user_corp_dept(user['id'], processed_user['enterprise_id'], dept_id, dest_cursor)
                else:
                    processed_user['user_name'] = sc['truck_driver_telephone']
                    processed_user['user_code'] = 'UC' + sc['truck_driver_telephone']
                    # insert sys_user
                    user_id = insert_user(processed_user, dest_cursor)
                    processed_user['user_id'] = user_id

            # insert sys_corp
            insert_corp(processed_user, dest_cursor)

            # insert sys_corp_log
            insert_corp_log(processed_user, dest_cursor)

            # corp_app_role
            insert_corp_app_role(processed_user['enterprise_id'], processed_user['user_code'],
                                 processed_user['user_id'], roles, dest_cursor)

            count += 1
            print(count)

            # 分批提交
            if count % batch_size == 0:
                dest_conn.commit()
                print(f"已同步 {count} 条记录")

        dest_conn.commit()  # 提交剩余记录
        print(f"同步完成！总计: {count} 条记录")

    except mysql.connector.Error as err:
        print(f"数据库错误: {err}")
        dest_conn.rollback()
    finally:
        # 关闭连接
        if src_cursor: src_cursor.close()
        if dest_cursor: dest_cursor.close()
        if src_conn: src_conn.close()
        if dest_conn: dest_conn.close()


def insert_corp(processed_user, dest_cursor):
    sql = """
                INSERT INTO sys_corp (id,
                                   corp_code,
                                   corp_name,
                                   user_code,
                                   linkman, -- 联系人
                                   linkman_mobile, -- 联系人电话
                                   corp_legalperson, -- 法人
                                   corp_legalperson_contact, -- 法人电话
                                   corp_legalperson_id_number, -- 法人证件号
                                   linkman_idnum, -- 联系人证件号
                                   union_society_code, -- 统一社会信用代码
                                   remark,
                                   deleted_tag,
                                   create_user,
                                   create_date,
                                   change_user,
                                   change_date,
                                   check_user_cd,
                                   check_date,
                                   check_state, -- pass
                                   register_type, -- person
                                   business_id,
                                   create_user_name,
                                   change_user_name,
                                   check_start_date,
                                   check_end_date,
                                   operation_type, -- insert
                                   submit_by,
                                   submit_by_name,
                                   submit_date,
                                   enterprise_type) 
                VALUES (
                    %(enterprise_id)s, %(enterprise_id)s, %(enterprise_name)s, %(user_code)s, %(contacter)s, %(contacter_phone)s, 
                    %(enterprise_name)s, %(truck_driver_telephone)s, %(truck_driver_idcardno)s, null, null, 'sync', %(deleted_tag)s, 
                    'admin', NOW(), 'admin', NOW(), 'U000000000000001', NOW(), 'pass', 'person', null, '系统', '系统', null, null, 'insert', %(user_code)s,
                    %(enterprise_name)s, NOW(), 'Personal'
                )
            """

    dest_cursor.execute(sql, processed_user)


def insert_corp_log(processed_user, dest_cursor):
    sql = """
                INSERT INTO sys_corp_log (id, corp_id,
                                   corp_code,
                                   corp_name,
                                   user_code,
                                   linkman, -- 联系人
                                   linkman_mobile, -- 联系人电话
                                   corp_legalperson, -- 法人
                                   corp_legalperson_contact, -- 法人电话
                                   corp_legalperson_id_number, -- 法人证件号
                                   linkman_idnum, -- 联系人证件号
                                   union_society_code, -- 统一社会信用代码
                                   remark,
                                   deleted_tag,
                                   create_user,
                                   create_date,
                                   change_user,
                                   change_date,
                                   check_user_cd,
                                   check_date,
                                   check_state, -- pass
                                   register_type, -- corp
                                   business_id,
                                   create_user_name,
                                   change_user_name,
                                   check_start_date,
                                   check_end_date,
                                   operation_type, -- insert
                                   submit_by,
                                   submit_by_name,
                                   submit_date,
                                   enterprise_type) 
                VALUES (
                    %(guid)s, %(enterprise_id)s, %(enterprise_id)s, %(enterprise_name)s, %(user_code)s, %(contacter)s, %(contacter_phone)s, 
                    %(enterprise_name)s, %(truck_driver_telephone)s, %(truck_driver_idcardno)s, null, null, 'sync', %(deleted_tag)s, 
                    'admin', NOW(), 'admin', NOW(), 'U000000000000001', NOW(), 'pass', 'person', null, '系统', '系统', null, null, 'insert', %(user_code)s,
                    %(enterprise_name)s, NOW(), 'Personal'
                )
            """
    dest_cursor.execute(sql, processed_user)


def insert_dept(depart_name, corp_code, dest_cursor):
    id = str(uuid.uuid4())
    sql = """
        INSERT INTO sys_depart (id, depart_name, state, iz_leaf, create_date, corp_code, remark, deleted_tag)
        values (%(id)s, %(depart_name)s, '1', '0', NOW(), %(corp_code)s, 'sync', 'N')
    """
    dest_cursor.execute(sql, ({'id': id, 'depart_name': depart_name, 'corp_code': corp_code}))

    return id


def insert_user(processed_user, dest_cursor):
    print("insert user: {}".format(processed_user['user_code']))
    sql = """
                INSERT INTO sys_user (user_name,
                                   real_name,
                                   password,
                                   phone, -- 电话
                                   state, -- 1
                                   create_user,
                                   create_date,
                                   change_user,
                                   change_date,
                                   corp_code,
                                   user_code,
                                   user_type, -- person
                                   driver_user_type, -- driver
                                   id_card, -- 身份证号
                                   second_check_flag, -- closed
                                   tenant_id, -- 010
                                   remark,
                                   deleted_tag,
                                   depart_ids
                ) values (
                    %(user_name)s, %(enterprise_name)s, '$2a$10$8bugIvDz88M.akjYljbC4e87FSBE02vEGhis4LQeqdE9Hv1eJPg3S', 
                    null, '1', 'admin', NOW(), 'admin', NOW(), %(enterprise_id)s, %(user_code)s, 'person', 'driver', 
                    null, 'closed', '010', 'sync', %(deleted_tag)s, %(depart_ids)s
                )
            """
    dest_cursor.execute(sql, processed_user)
    return dest_cursor.lastrowid


def update_user_corp_dept(user_id, corp_code, dept_id, dest_cursor):
    print(f'update user corp_code = {corp_code} for user_id = {user_id}')
    sql = "UPDATE sys_user SET corp_code = %s, depart_ids = %s where id = %s;"
    dest_cursor.execute(sql, (corp_code, dept_id, user_id))


def query_default_role(dest_conn):
    try:
        cursor = dest_conn.cursor(dictionary=True)

        sql = "select * from sys_role where default_flag = %s"
        cursor.execute(sql, ('1',))
        row_dict = cursor.fetchall()
        return row_dict
    finally:
        if cursor: cursor.close()


def query_user(user_name, dest_conn):
    try:
        cursor = dest_conn.cursor(dictionary=True)

        sql = "select * from sys_user where user_name = %s"
        cursor.execute(sql, (user_name,))
        row_dict = cursor.fetchone()
        return row_dict
    finally:
        if cursor: cursor.close()


def insert_corp_app_role(corp_code, user_code, user_id, roles, dest_cursor):
    # print('insert_corp_app_role')
    dicts = []
    uc = {'app_code': 'uc', 'corp_type': 'uc_user', 'corp_code': corp_code}
    dicts.append(uc)
    wl = {'app_code': 'wl', 'corp_type': 'wl_driver', 'corp_code': corp_code}
    dicts.append(wl)
    main = {'app_code': 'main', 'corp_type': 'main_driver', 'corp_code': corp_code}
    dicts.append(main)
    ymt = {'app_code': 'ymt', 'corp_type': 'ymt_driver', 'corp_code': corp_code}
    dicts.append(ymt)

    insert_sql = """
        INSERT INTO sys_corp_app_role (app_code, corp_type, corp_code) VALUES (%(app_code)s, %(corp_type)s, %(corp_code)s)
    """

    # user = query_user(dest_conn, processed_user['truck_driver_telephone'])
    for c in dicts:
        # sys_corp_app_role
        dest_cursor.execute(insert_sql, c)
        # sys_user_app
        insert_user_app(c['app_code'], user_code, dest_cursor)
        # sys_user_role
        insert_default_role(c, user_code, user_id, roles, dest_cursor)

    # insert_reg_user_role
    insert_user_role(102, user_code, user_id, dest_cursor)


def insert_user_app(app_code, user_code, dest_cursor):
    # print('insert_user_app')
    insert_sql = """
        INSERT INTO sys_user_app(user_code, app_code) VALUES (%s, %s)
    """

    dest_cursor.execute(insert_sql, (user_code, app_code))


def insert_default_role(c, user_code, user_id, roles, dest_cursor):
    # print('insert_default_role')

    # roles = query_default_role(dest_conn)
    for role in roles:
        if role['app_code'] == c['app_code'] and role['role_group'] == c['corp_type']:
            insert_user_role(role['id'], user_code, user_id, dest_cursor)


def insert_user_role(role_id, user_code, user_id, dest_cursor):
    insert_sql = """
            INSERT INTO sys_user_role(id, role_id, user_code, user_id) VALUES (%s, %s, %s, %s)
        """
    dest_cursor.execute(insert_sql, (str(uuid.uuid4()), role_id, user_code, user_id))


if __name__ == "__main__":
    sync_driver_data()
